﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.VR.Params;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRUserLevelLogDAL: IT_VRUserLevelLogDAL
    {
        /// <summary>
        ///  验证用户是否需要重新计算用户等级
        ///  大于0：需要重新计算用户等级
        /// </summary>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public int CheckCountScore(int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM 
            (SELECT TOP 1 create_date FROM dbo.T_VRUserLevelLog WHERE user_id={user_id} ORDER BY create_date DESC)logs 
            WHERE logs.create_date<CONVERT(DATETIME, '31/12/'+CAST(YEAR(GETDATE()) AS VARCHAR(5)),103);");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取销售金额
        /// </summary>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public decimal getSellNum(int user_id,int year)
        {
            year = year < 0 ? DateTime.Now.Year : year;
            sb?.Clear();
            sb.Append($@"SELECT ISNULL(SUM(gold_num),0)AS num FROM dbo.T_VRGoldLog WHERE FolderId>0 AND operator_type in(1,3) AND create_id={user_id} AND gold_type=1 AND create_date>='{year}-01-01' AND create_date<='{year}-12-31'");
            return Convert.ToDecimal(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取用户等级
        /// </summary>
        /// <param name="lineNum">下线人数</param>
        /// <param name="sellNum">销售金额</param>
        /// <param name="folder_id">店铺id</param>
        /// <returns></returns>
        public int getUserLevel(int lineNum, decimal sellNum,int folder_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP 1 user_level FROM dbo.T_VRUserLevel WHERE min_sale<={sellNum} AND max_sale>={sellNum} AND min_line<=(case when min_line>0 then {lineNum} else 0 end) AND max_line>=(case when max_line>0 then {lineNum} else 0 end) AND (store_id={folder_id} OR store_id=0) ORDER BY store_id DESC;");
            object obj = SqlHelper.ExecuteScalar(sb.ToString());
            if (obj == null)
                return 0;
            return Helper.ToInt(obj);
        }
        /// <summary>
        ///  获取商品折扣
        /// </summary>
        /// <param name="user_level">用户等级</param>
        /// <param name="file_id">商品id</param>
        /// <param name="folder_id">店铺id</param>
        /// <returns></returns>
        public decimal GetGoodRate(int user_level, int file_id, int folder_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP 1 r.rate FROM  
            (SELECT rate,store_id,file_id=(CASE WHEN file_id<>{file_id} THEN 0 ELSE file_id END)  FROM dbo.T_VRGoodDiscount 
            WHERE user_level={user_level} AND(file_id={file_id} OR store_id={folder_id} OR store_id=0))r ORDER BY r.file_id DESC,r.store_id DESC;");
            object obj = SqlHelper.ExecuteScalar(sb.ToString());
            if (obj == null)
                return 0;
            return Helper.ToDecimal(obj);
        }
        /// <summary>
        /// 获取用户等级日志列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<UserLevelLog> GetUserLevelLog(UserLevelLogParam param, out int total)
        {
            sb?.Clear();
            string whereSql = GetLevelSql(param);
            total = Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0)AS num FROM T_VRUserLevelLog WHERE 1=1 " + whereSql));
            sb.Append($@"SELECT TOP {param.rows} logs.*,f.folder_name,u.login_name FROM (SELECT id,user_id,user_name,user_level,sell_money,line_num,create_id,create_name,folder_id,
CONVERT(varchar(100),create_date,120) AS create_date,ROW_NUMBER() OVER(ORDER BY create_date DESC)AS number FROM dbo.T_VRUserLevelLog WHERE 1=1");
            sb.Append(whereSql);
            sb.Append($")logs inner join (select user_id,login_name from T_User)u on logs.user_id=u.user_id LEFT JOIN (SELECT folder_id,folder_name FROM dbo.T_DocumentFolder)f ON logs.folder_id=f.folder_id WHERE logs.number>{(param.page - 1) * param.rows}");
            return SqlHelper.ExecuteList<UserLevelLog>(sb.ToString());
        }
        private string GetLevelSql(UserLevelLogParam param)
        {
            StringBuilder _sb = new StringBuilder();
            //用户名
            if (!string.IsNullOrEmpty(param.userName))
                _sb.Append($" AND user_name='{param.userName}' ");
            //销售额
            if (param.minScore > 0 && param.maxScore > 0)
                _sb.Append($@" AND sell_money>={param.minScore} AND sell_money<={param.maxScore} ");
            else if (param.minScore > 0 && param.maxScore <= 0)
                _sb.Append($@" AND sell_money>={param.minScore} ");
            else if (param.minScore <= 0 && param.maxScore > 0)
                _sb.Append($@" AND sell_money<={param.maxScore} ");
            //下限人数
            if (param.minLine > 0 && param.maxLine > 0)
                _sb.Append($@" AND line_num>={param.minLine} AND line_num<={param.maxLine} ");
            else if (param.minScore > 0 && param.maxScore <= 0)
                _sb.Append($@" AND line_num>={param.minLine} ");
            else if (param.minScore <= 0 && param.maxScore > 0)
                _sb.Append($@" AND line_num<={param.maxLine} ");
            //用户等级
            if (param.userLevel > 0)
                _sb.Append($" AND user_level={param.userLevel} ");
            return _sb.ToString();
        }
    }
}